PDO with Prepared Statements a Complete Reference Guide

One of the common mistake made by beginner level PHP developers is use of the old insecure mysql extension to connect to MySQL database. This leads to very serious security vulnerabilities like SQL Injection in their websites. I am not blaming junior developers because I MYSELF do the same mistake in my first website. The main reason for this is although official PHP documentation is extremely standardised it is a mess for beginners so many starters go for the old unstandardized blog for referral and end up with using age old unstandardized codes. I know personally many developers who still use deprecated mysql extension instead of new improved mysqli extension or PDO allows you to access the functionality provided by MySQL 4.1 and above. This is the main reason why I am making this tutorial so that any beginner who starts their programming in PHP can use new database independent interface for accessing databases in PHP. To ensure maximum security and to prevent SQL Injection attacks I am using Prepared Statements and Bind parameters in this tutorial but trust me I try to make this one as simple as possible.

What is PDO?

PDO stands for  PHP Data Objects which is a lightweight, consistent interface for accessing databases in PHP. Unlike mysqli interface, it is database independent so if you learn PDO you can switch to any database in future. Anyway, i am not going to describe all advantages of it in this tutorial which is out of context and unnecessary.

Why Prepared Statements and Bind Parameters? 

The PDO with Prepared statements and Bind Parameters is to remove malicious code from the user input and thus to prevent us from SQL Injection. Binding datatype to user input using bind parameter ensure that only specified datatype with specified length is accepted. It ensures that attackers cannot insert string datatype in fields which only require integer values.

Now Let's start coding.

Connecting to Database

Before we start interacting with our database we need to establish the connection to the database. In case of PDO you need to create one object per database you would like to interact with. Here is the syntax for that.

$conn = new PDO("mysql:host=$DB_HOST;port=3306;dbname=$DB_NAME", $DB_USER, $DB_PASSWORD);

For simplicity and code reusability we are going to include all code for connection establishment inside a connect.php file and include this file in all PHP pages which require database operation by using require 'connect.php'; statement at the beginning of the page. The complete code for connect.php file is given below.

<?php
					
$DB_HOST = "localhost";
$DB_NAME = "bookstore"; 
$DB_USER = "bookstore"; 
$DB_PASSWORD = "book123";

try{
    $conn = new PDO("mysql:host=$DB_HOST;port=3306;dbname=$DB_NAME", $DB_USER, $DB_PASSWORD);

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  // To turn on error mode for debugging errors
	$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);  // To get maximum sql injection protection
   // echo "Connected successfully";
} 
catch(PDOException $e){
    echo "Connection failed: " . $e->getMessage();
}

Note: For development/learning, it’s a good idea to set the PDO error mode attribute PDO::ERRMODE_EXCEPTION. This will throw an exception if there is an error. We must also set the PDO::EMULATE_PREPARES to false to prevent SQL Injection attacks. I also specify the port number in the connection code which is not required if you are using localhost but you need to specify it if you are connecting to some remote production environment with different settings. By default, the MySQL port number is 3306. 

Retrieving all Results

In order to retrieve all data from database in PDO without any where condition, we can simply use query() method instead of secure prepare statements since we are not accepting any user input which may cause SQL Injection attacks. The syntax for query() method is given below.

$query = $db->query(QUERY);

Now let's look at the complete code for a sample index function which fetches 5 books details from book table.

function index()
{
	global $conn;

	$query = $conn->query("SELECT title,author,price FROM book ORDER BY price DESC LIMIT 5");

	$result = $query->fetchAll();

	echo "<b>Index Page</b> ( Total No of Results : ".$query->rowCount()." )</br>";
	foreach($result as $object)
	{
		echo "</br>Title : ".$object['title']."</br>Auther : ".$object['author']."</br> Price : ".$object['price']."</br>";	 
	}
}

The fetchAll() method is used to fetch all results. If you have only one row then you can use fetch() method instead. The fetchAll() method and fetch() method return results as an associative array format by default. I used foreach statement for getting each row.

You can use $query->rowCount() method to get the count of the number of rows.

Fetching a single value 

Here we are going to use prepared statement with bind parameters since there is a where condition in our query which accept user input value. The code for a sample show function which retrieves a book with a specific book id and name is given below.

function show($id,$title)
{
	global $conn;
	
	$query = $conn->prepare('SELECT title,author,price FROM book WHERE bookid = ? AND title = ?');
		
	$query->bindParam(1, $id, PDO::PARAM_INT);
    $query->bindParam(2, $title, PDO::PARAM_STR, 50);
	
	$query->execute();

	$result = $query->fetch();

	echo "</br><b>Show Page</b></br>";
	
    echo "</br>Title : ".$result['title']."</br>Auther : ".$result['author']."</br> Price : ".$result['price']."</br>";	 
	
}

Note:  Here I used the bindParam function to ensure that id accepts only integer value and length of title must not greater than 50 characters. 

The output image of above two function is given below.

Insertion

The insertion code also requires prepared statements and bind parameters since it is accepting user input. The complete code for insert function is given below.

function insert($title,$author,$price)
{
	global $conn;
	
	$query = $conn->prepare('INSERT INTO book (title,author,price) VALUES (?, ?, ?)');

	$query->bindParam(1,$title, PDO::PARAM_STR, 50);
	$query->bindParam(2,$author, PDO::PARAM_STR, 25);
	$query->bindParam(3,$price, PDO::PARAM_INT);

	$query->execute();
   
}

Note:  If you are storing price then probably you think need to store it as decimal value then you need to store it as string as bindParam function did not support decimal values.

Updation

The complete code for update function is given below.

function update($price,$title,$author)
{
	global $conn;
	
	$query = $conn->prepare('UPDATE book SET price= ? WHERE title = ? AND author = ?');

    $query->bindParam(1,$price, PDO::PARAM_INT);
	$query->bindParam(2,$title, PDO::PARAM_STR, 50);
	$query->bindParam(3,$author, PDO::PARAM_STR, 25);

	$query->execute();
   
}

Deletion

The complete code for delete function is given below.

function delete($title)
{
	global $conn;
	
	$query = $conn->prepare('DELETE FROM book WHERE title = ?');

	$query->bindParam(1,$title, PDO::PARAM_STR, 50);

	$query->execute();
   
}

Please note that I did not include all method of PDO in this tutorial because it is unnecessary for a beginner to study every function for creating a web page with some simple basic operation like create read update and delete. My primary aim was to make this tutorial as simple as possible. I personally believe that you need not need to learn everything to create something. All you need is a will to create something and just start it. After all

Training is Nothing Will is Everything

Comment below if you need any help or if you have any suggestions.